package cn.lysj.framework.order.repository;

import cn.lysj.framework.base.BaseRepository;
import cn.lysj.framework.order.domain.FoodOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @author zwj
 * @description 订单数据处理层
 * @create 2018-11-05 09:51:42
 **/
public interface FoodOrderRepository extends BaseRepository<FoodOrder> {

    Page<FoodOrder> findByCustomerIdAndStoreIdAndFoodOrderStatusNotInOrderByCreateTimeDesc(Pageable pageable, String customerId, String storeId, Integer[] status);

    FoodOrder findByOtherOrderNumber(String otherOrderNumber);

    List<FoodOrder> findByStoreIdAndFoodOrderStatusNot(String storeId, Integer foodOrderStatus);

    Page<FoodOrder> findByStoreIdAndFoodOrderStatusIn(Pageable pageable, String storeId, Integer[] foodOrderStatus);

    Page<FoodOrder> findByStoreIdAndFoodOrderStatus(Pageable pageable, String storeId, Integer foodOrderStatus);

    List<FoodOrder> findByPrintTypeAndStoreIdAndFoodOrderStatusInOrderByCreateTimeDesc(Integer printType, String storeId, Integer[] foodOrderStatus);


    @Query("select a from FoodOrder a where a.storeId = ?1 and a.foodOrderStatus in ?2 and a.createTime >= ?3 and a.createTime <?4")
    List<FoodOrder> getNewFoodOrders(String storeId, Integer[] foodOrderStatus, Date beginTime, Date endTime);

    //支付回调使用
    FoodOrder findByFoodOrderNumber(String foodOrderNumber);

    //按天查询
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,sum(act_pay_money) sum_money,count(*) FROM food_order   WHERE create_time " +
            "BETWEEN ?1  AND ?2 AND store_id=?3 AND food_order_status=2  GROUP BY days ASC ", nativeQuery = true)
    List<Object[]> findByDay(Date begin_time, Date end_time, String store_id);

    //按天查询
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,sum(act_pay_money) sum_money,count(*) FROM food_order   WHERE create_time " +
            "BETWEEN ?1  AND ?2 AND store_id=?3 AND food_order_status=2  GROUP BY days DESC ", nativeQuery = true)
    List<Object[]> findByDayDesc(Date begin_time, Date end_time, String store_id);


    //根据周查询
    @Query(value = "SELECT CONCAT(YEAR(create_time),'-',DATE_FORMAT(create_time,'%u')) weeks,SUM(act_pay_money) AS count_amount,count(*) FROM food_order " +
            "WHERE `create_time` BETWEEN ?1 AND ?2 AND store_id = ?3 AND  food_order_status=2 GROUP BY weeks ORDER BY weeks ASC ", nativeQuery = true)
    List<Object[]> findByWeek(Date begin_time, Date end_time, String store_id);

    //按月查询
    @Query(value = "SELECT CONCAT(YEAR(create_time),'-',DATE_FORMAT(create_time,'%m')) months ,SUM(act_pay_money) AS count_amount ,count(*) FROM food_order " +
            "WHERE `create_time` BETWEEN ?1 AND ?2 AND store_id=?3 AND  food_order_status=2 GROUP BY months  ASC ", nativeQuery = true)
    List<Object[]> findByMonth(Date begin_time, Date end_time, String store_id);

    //根据季度查询
    @Query(value = "SELECT CONCAT(YEAR(create_time),'-',quarter(create_time)) qu,SUM(act_pay_money) AS count_amount ,count(*) FROM food_order WHERE `create_time` " +
            "BETWEEN ?1 AND ?2 AND store_id=?3 AND  food_order_status=2 GROUP BY qu ASC ", nativeQuery = true)
    List<Object[]> findByQu(Date begin_time, Date end_time, String store_id);

    //按年查询
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y')  years, SUM(act_pay_money) AS count_amount ,count(*) FROM food_order WHERE `create_time` " +
            "BETWEEN ?1 AND ?2 AND store_id=?3 AND  food_order_status=2 GROUP BY years ASC ", nativeQuery = true)
    List<Object[]> findByYear(Date begin_time, Date end_time, String store_id);

    //模板消息推送
    @Query(value = "SELECT store_id, count(*) FROM food_order  WHERE  print_type=1 AND create_time<?1 GROUP BY  store_id ",nativeQuery = true)
    List<Object[]> countPlatOrderNum(String time);

    //---------------财务管理---------------------------
    //订单数量
    int countByStoreIdAndFoodOrderStatus(String storeId,Integer orderStatus);

    @Query(value = "select  sum(act_pay_money) from food_order where store_id=?1 AND  food_order_status=2 AND suppory_pay_way=?2", nativeQuery = true)
    BigDecimal summaryMoney(String storeId, Integer supportWay);

    //-------------日汇总收银统计------------

    //线下支付
    @Query(value = "SELECT  DATE_FORMAT(create_time,'%Y-%m-%d') days,SUM(act_pay_money) FROM food_order WHERE store_id=?1  AND food_order_status=2 AND suppory_pay_way=2  AND " +
            "(create_time BETWEEN ?2 AND ?3)   GROUP BY days", nativeQuery = true)
    List<Object[]> daySummaryOffCashier(String storeId, Date begin_date, Date end_date);

    //线上支付
    @Query(value = "select  DATE_FORMAT(create_time,'%Y-%m-%d') days,sum(act_pay_money) from food_order where store_id=?1  AND food_order_status=2 AND suppory_pay_way=1 AND pay_way=?2 AND " +
            "(create_time between ?3 and ?4)   GROUP BY days", nativeQuery = true)
    List<Object[]> daySummaryOnLineCashier(String storeId, Integer payWay, Date begin_date, Date end_date);


    //---------------------订单查询-------------------
    @Query(value = "SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,COUNT(*) FROM food_order WHERE " +
            "store_id=?1 AND food_order_status=2 AND repast_way=?2 AND (create_time BETWEEN ?3 AND ?4)   GROUP BY days", nativeQuery = true)
    List<Object[]> platOrderNum(String storeId, Integer repasteWay, Date begin_date, Date end_date);


    List<FoodOrder> findByStoreIdAndFoodOrderStatusAndCreateTimeBetween(String storeId,Integer foodOrderStatus,Date begin_date, Date end_date);
}
